﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace docmgr.DAL
{

    /// <summary>
    /// Document 的摘要说明
    /// </summary>
    public partial class Document
    {
        /// <summary>
        /// 当前表的名称
        /// </summary>
        public const string _tableName = "Document";
        public const string _fristKey = "ID";
        /// <summary>
        /// 默认数据库连接字符串
        /// </summary>
        private string connectionString =  ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        //声明子表DAL类,如果有的话,可以声明多个副表的类
        //docmgr.DAL.LeaveInfo detailsDAL;

        #region  成员方法组1：对表进行查询,添加,修改,删除操作

        #region 对表进行查询 (有3个方法)
        /// <summary>
        /// 根据SQL命令获取表的一条或多条记录
        /// </summary>
        /// <param name="sql">SQL查询命令</param>
        /// <returns></returns>
        public System.Data.DataTable GetTable(string sql)
        {
            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();

            _adapter.SelectCommand.CommandText = sql;

            System.Data.DataTable dt = new System.Data.DataTable(_tableName);
            _adapter.Fill(dt);
            this._connection.Close();
            return dt;
        }

        /// <summary>
        /// 根据SQL命令获取一个表的分页记录
        /// </summary>
        /// <param name="sql">SQL查询命令</param>
        /// <param name="PageSize">分页大小</param>
        /// <param name="PageIndex">页码</param>  
        /// <returns></returns>
        public System.Data.DataTable GetTable(string sql, int PageSize, int PageIndex)
        {
            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();

            System.Data.DataSet ds = new System.Data.DataSet();
            _adapter.SelectCommand.CommandText = sql;
            _adapter.Fill(ds);
            this._connection.Close();
            return ds.Tables[0];
        }

        /// <summary>
        /// 根据一组SQL命令获取一个DataSet
        /// (可以获取多个表的多条记录)
        /// </summary>
        /// <param name="sqlList">一组SQL命令</param>
        /// <returns></returns>
        public System.Data.DataSet GetDataSet(IList<string> sqlList)
        {

            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();
            System.Data.DataSet ds = new System.Data.DataSet();
            _adapter.SelectCommand.CommandText = sqlList[0];//第一个sql命令默认获取主表记录
            _adapter.Fill(ds);
            this._connection.Close();
            return ds;
        }
        #endregion

        #region  对表进行添加,修改 (有2个方法)
        /// <summary>
        /// 更新一个表格
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public int Update(System.Data.DataTable table)
        {
            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();
            int returnValue = this._adapter.Update(table);
            this._connection.Close();
            return returnValue;
        }

        /// <summary>
        /// 使用事务
        /// 更新一个表格
        /// </summary>
        /// <param name="table"></param>
        /// <returns></returns>
        public int UpdateWithTrans(System.Data.DataTable table)
        {
            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();
            this.BeginTransaction();
            try
            {
                int returnValue = this._adapter.Update(table);
                this.CommitTransaction();//提交
                this._connection.Close();
                return returnValue;
            }
            catch
            {
                this.RollbackTransaction();//回滚
                this._connection.Close();
                //throw;
                return 0;
            }
        }
        #endregion

        #region 对表进行删除 (有5个方法:Delete(string),DeleteWithTrans(string),DeleteByKey(string),DeleteByKey(List<string>), DeleteByKey(List<string>)
        /// <summary>
        /// 通过自定义条件删除一条或多条记录
        /// </summary>
        /// <param name="sql">SQL命令</param>
        /// <returns>返回删除的行数</returns>
        public int Delete(string sql)
        {
            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();
            SqlCommand command = new SqlCommand(sql, _connection);
            int retint = command.ExecuteNonQuery();
            this._connection.Close();
            return retint;           
        }

        /// <summary>
        /// 使用事务
        /// 通过自定义条件删除一条或多条记录
        /// </summary>
        /// <param name="sql">SQL命令</param>
        /// <returns>返回删除的行数</returns>
        public int DeleteWithTrans(string sql)
        {
            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();
            SqlCommand command = new SqlCommand(sql, _connection);
            int retint = command.ExecuteNonQuery();
            this._connection.Close();
            return retint;        
        }
        /// <summary>
        /// 通过一个主键删除一条记录
        /// </summary>
        /// <param name="keys">一个主键值</param>
        /// <returns>返回删除的行数</returns>
        public int DeleteByKey(string key)
        {
            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();
            _adapter.DeleteCommand.CommandText = key;
            this._connection.Close();
            return 0;
        }
        /// <summary>
        /// 通过一组主键删除多条记录
        /// </summary>
        /// <param name="keys">一组主键</param>
        /// <returns>返回删除的行数</returns>
        public int DeleteByKey(System.Collections.Generic.List<string> keys)
        {
            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();
            foreach (string key in keys)
            {
                _adapter.DeleteCommand.CommandText = "";
            }
            this._connection.Close();
            return 0;
        }

        /// <summary>
        /// 使用事务
        /// 通过一组主键值删除多条记录
        /// </summary>
        /// <param name="keys">一组主键值</param>
        /// <returns>返回删除的行数</returns>
        public int DeleteByKeyWithTrans(System.Collections.Generic.List<string> keys)
        {
            if (this._transaction == null)
            {
                return 0;
            }
            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();
            this.BeginTransaction();
            try
            {
                foreach (string key in keys)
                {
                    //_adapter.Delete(id);
                }
                this.CommitTransaction();
                this._connection.Close();
            }
            catch
            {
                this.RollbackTransaction();
                this._connection.Close();
                throw;
            }
            return 0;
        }
        #endregion

        #endregion

        #region 成员方法组2： 主副表操作 (有3个方法)
        // 以下是主副表操作, [4/8/2005 Administrator]
        // 获取一个主副表     GetMain2DetailsDataSet
        // 使用事务更新主副表 UpdateMain2DetailsWithTrans
        // 使用事务删除主副表 DeleteMain2DetailsByKeyWithTrans
        // 如果没有主副表可以 不使用这三个方法

        /// <summary>
        /// 根据一组SQL命令获取一个主副表DataSet
        /// </summary>
        /// <param name="sqlList">一组SQL命令,第一个sql命令默认获取主表记录</param>
        /// <returns></returns>
        public System.Data.DataSet GetMain2DetailsDataSet(IList<string> sqlList)
        {

            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();

            //创建副表DAL
            //detailsDAL = new docmgr.DAL.detailsInfo();
            //detailsDAL.Connection = this._connection;
            //detailsDAL.CreateAdapter();

            this.BeginTransaction();
            //detailsDAL.Transaction = this._transaction;//给副表DAL指定事务
            try
            {
                System.Data.DataSet ds = new System.Data.DataSet();
                ds.Tables.Add(this.GetTable(sqlList[0]).Copy());//第一个sql命令获取主表记录
                //ds.Tables.Add(detailsDAL.GetTable(sqlList[1]).Copy());//第二个sql命令获取副表记录
                this.CommitTransaction();//提交
                this._connection.Close();
                return ds;
            }
            catch
            {
                this.RollbackTransaction();//回滚
                this._connection.Close();
                //throw;
                return null;
            }
        }

        /// <summary>
        /// 使用事务更新主副表DataSet
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool UpdateMain2DetailsWithTrans(System.Data.DataSet ds, out string msg)
        {
            msg = "";
            bool returnValue = false;

            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();

            //创建副表DAL
            //detailsDAL = new docmgr.DAL.detailsInfo();
            //detailsDAL.Connection = this._connection;
            //detailsDAL.CreateAdapter();

            this.BeginTransaction();
            //detailsDAL.Transaction = this._transaction;//给副表DAL指定事务

            try
            {
                int retint = 0;
                //如果包含主表
                if (ds.Tables.Contains(_tableName))
                {
                    retint += this.Update(ds.Tables[_tableName]);
                }
                //如果包含副表
                //if (ds.Tables.Contains("details"))
                //{
                //    retint += details.Update(ds.Tables["details"]);
                //}
                this.CommitTransaction();//提交
                this._connection.Close();
                returnValue = true;
            }
            catch (Exception e)
            {
                msg = e.Message;
                this.RollbackTransaction();//回滚
                this._connection.Close();
                returnValue = false;
            }
            return returnValue;
        }

        /// <summary>
        /// 使用事务
        /// 通过主表主键值删除主副表
        /// </summary>
        /// <param name="key">主表主键</param>
        /// <param name="msg">返回消息</param>
        /// <returns></returns>
        public bool DeleteMain2DetailsByKeyWithTrans(string key, out string msg)
        {
            msg = "";
            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();
            bool returnValue = false;
            //创建副表DAL
            //detailsDAL = new docmgr.DAL.detailsInfo();
            //detailsDAL.Connection = this._connection;
            //detailsDAL.CreateAdapter();
            this.BeginTransaction();
            //detailsDAL.Transaction = this._transaction;//给副表DAL指定事务
            try
            {
                int retint = 0;//删除行数计数器
                //删除主表
                retint += this.DeleteByKey(key);

                //删除副表
                //string sql = string.Format("Delete from {0} where ID='{1}'", "details", key);
                //retint += detailsDAL.Delete(sql);

                this.CommitTransaction();//提交
                this._connection.Close();
                returnValue = true;

            }
            catch (Exception e)
            {
                msg = e.Message;
                this.RollbackTransaction();//回滚
                this._connection.Close();
                returnValue = false;
            }
            return returnValue;
        }
        #endregion

        #region 成员方法组3：你增加的新方法
        //
        //
        //
        //
        #endregion

        public DataSet GetPage(string fields, string condition, int pageIndex, int pageSize)
        {
            SqlCommand command = new SqlCommand("paging", this.Connection);
            command.CommandType = CommandType.StoredProcedure;

            String table = "Document AS doc LEFT JOIN Department AS dep ON doc.DepartmentID = dep.ID LEFT JOIN [User] AS u1 ON doc.UserID = u1.id left join [User] as u2 on doc.HandleUserID=u2.id";

            table = "dbo.Document AS doc LEFT JOIN dbo.[User] AS u ON doc.UserID = u.ID LEFT JOIN dbo.Department AS dep ON doc.DepartmentID = dep.ID LEFT JOIN dbo.Directory AS dir ON doc.DocClassID = dir.ID";
            command.Parameters.Add("@Table", SqlDbType.NVarChar, 1000).Value = table;// "Document";//对应的数据表名
            command.Parameters.Add("@TIndex", SqlDbType.NVarChar, 100).Value = "doc.ID";//主键ID
            command.Parameters.Add("@Column", SqlDbType.NVarChar, 2000).Value = fields;// " doc.ID,doc.DocName,doc.RegDate,doc.Status,u1.name 责任人,doc.DocSerialNumber,doc.SecretLevel,dep.name 文件单位,doc.DocType,doc.PageNumber,doc.Code,u2.name 持有人,doc.Remark  ";//要查询的字段，*为全部字段
            command.Parameters.Add("@Sql", SqlDbType.NVarChar, 3000).Value = condition;//查询条件
            command.Parameters.Add("@PageIndex", SqlDbType.Int, 8).Value = pageIndex.ToString();//当前页码
            command.Parameters.Add("@PageSize", SqlDbType.Int, 8).Value = pageSize.ToString();//每一页显示的行数
            command.Parameters.Add("@Sort", SqlDbType.NVarChar, 200).Value = "";// 默认为空


            SqlDataAdapter da = new SqlDataAdapter(command);

            DataSet ds = new DataSet();

            da.Fill(ds);

            if (ds.Tables[0].Columns.Contains("RowNum"))
                ds.Tables[0].Columns.Remove("RowNum");

            return ds;
        }
    }

    /// <summary>
    /// 请注意,
    /// 请勿修改本类的这部分内容
    /// </summary>
    public partial class Document
    {

        private SqlConnection _connection;
        private SqlDataAdapter _adapter;
        private SqlTransaction _transaction;
        /// <summary>
        /// 公共属性:Connection连接
        /// </summary>
        public SqlConnection Connection
        {
            get
            {
                return this._connection;
            }
            set
            {
                this._connection = value;
            }
        }
        /// <summary>
        /// 公共属性:Transaction事务
        /// </summary>
        public SqlTransaction Transaction
        {
            get
            {
                return this._transaction;
            }
            set
            {
                this._transaction = value; //如果连接不同就不能设置
                this._adapter.SelectCommand.Transaction = this.Transaction;
                this._adapter.InsertCommand.Transaction = this.Transaction; //给特殊command赋值事务
                this._adapter.UpdateCommand.Transaction = this.Transaction;
                this._adapter.DeleteCommand.Transaction = this.Transaction;
            }
        }
        /// <summary>
        /// 构造函数
        /// </summary>
        public Document()
        {

        }
        /// <summary>
        /// 重载构造函数
        /// 指定connect连接和初始化适配器
        /// </summary>
        /// <param name="connection"></param>
        public Document(SqlConnection connection)
        {
            _connection = connection;
            CreateAdapter();
        }

        /// <summary>
        /// 初始化连接和适配器
        /// </summary>
        public void InitiConnect2Adapter()
        {
            _connection = new SqlConnection(connectionString);
            CreateAdapter();
        }

        /// <summary>
        /// 创建适配器
        /// </summary>
        public void CreateAdapter()
        {
		    try {
               if (this._connection.State != ConnectionState.Open)
                   this._connection.Open();
			} catch (System.Exception ex) {
                Console.WriteLine(ex.StackTrace);
                return;	
            } 
            _adapter = new SqlDataAdapter();
            SqlCommand selectCommand = new SqlCommand("select top 1 * from [Document] where 0=1", _connection);
            _adapter.SelectCommand = selectCommand;
            SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(_adapter);
            //自动生成update,insert,delete语句 其唯一的要求，是查询字段中必须有一个是唯一字段。
            sqlBuilder.QuotePrefix = "[";
            sqlBuilder.QuoteSuffix = "]";
            //string sql1 = sqlBuilder.GetUpdateCommand().CommandText;
            //string sql2 = sqlBuilder.GetInsertCommand().CommandText;
            //string sql3 = sqlBuilder.GetDeleteCommand().CommandText;
            //       
            _adapter.InsertCommand = sqlBuilder.GetInsertCommand();
            _adapter.UpdateCommand = sqlBuilder.GetUpdateCommand();
            _adapter.DeleteCommand = sqlBuilder.GetDeleteCommand();
            //自动增长值的获取通过输出参数来完成
            //Select   @@indetity
            //_adapter.InsertCommand.CommandText += ";SET ID = SCOPE_IDENTITY()";//SELECT @@indetity As ID";//Select SCOPE_IDENTITY() As U_ID
            //_adapter.InsertCommand.Parameters.Add("@ID",SqlDbType.Int,4,"ID");
            //_adapter.InsertCommand.Parameters["@ID"].Direction = ParameterDirection.Output;

            _adapter.RowUpdated += new SqlRowUpdatedEventHandler(_adapter_RowUpdated);
            //sqlBuilder.Dispose();
        }
        /// <summary>
        /// 更新自动增长列，
        /// 但这种方式效率太低，提高了维护复杂度，请使用更好的方法。
        /// 给本类多增加了一个_fristKey变量，维护麻烦 (主键一改这里也要改，必须要有自动增长列)
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="args"></param>
        void _adapter_RowUpdated(object sender, SqlRowUpdatedEventArgs args)
        {
            SqlCommand idCMD = new SqlCommand("SELECT @@IDENTITY", this.Connection);
            if (this.Transaction != null)
                idCMD.Transaction = this.Transaction;
            if (args.StatementType == StatementType.Insert)
            {
                object onewID = idCMD.ExecuteScalar();
                if (onewID != null) args.Row[_fristKey] = Convert.ToInt32(onewID);
            }
        }
        /// <summary>
        /// 初始化事务
        /// </summary>
        public void BeginTransaction()
        {
            if (this._connection.State != ConnectionState.Open)
                this._connection.Open();
            this._transaction = this._connection.BeginTransaction();  //
            //将本类实例中的所有sqlcommand赋值事务.
            //foreach(SqlCommand command in this.CommandCollection) {               
            //    command.Transaction = this.Transaction;
            //}
            this._adapter.SelectCommand.Transaction = this.Transaction;
            this._adapter.InsertCommand.Transaction = this.Transaction; //给特殊command赋值事务
            this._adapter.UpdateCommand.Transaction = this.Transaction;
            this._adapter.DeleteCommand.Transaction = this.Transaction;
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            if (this._transaction != null)
            {
                this.Transaction.Commit(); // 
                this.Transaction.Dispose();
                //this._connection.Close();// 关闭connection连接
            }
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            if (this._transaction != null)
            {
                this.Transaction.Rollback(); // 
                this.Transaction.Dispose();
                //this._connection.Close(); // 关闭connection连接
            }

        }

    }
}
